﻿Imports System.Data.OleDb
Imports DTO
Public Class HocSinhDAO
    Dim ds As List(Of String)
    'Dim dataset As DataSet
#Region "LayBangHS"
    Public Function LayBangHS() As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String
        sql = "select *from HocSinh"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(sql, cn)
        'dataset = New DataSet()
        da.Fill(dt)
        'da.Fill(dataset, "HocSinh")
        cn.Close()
        Return dt
    End Function
#End Region
#Region "DSGioiTinh"
    Public Function DSGioiTinh() As List(Of String)
        Dim sql As String
        sql = "select DISTINCT GioiTinh from HocSinh"
        'Mo ket noi CSDL
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, cnn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim ds As List(Of String)
        ds = New List(Of String)
        While reader.Read()
            Dim gt As String
            gt = reader.GetString(0)
            ds.Add(gt)
        End While
        cnn.Close()
        Return ds
    End Function
#End Region
#Region "Them1HS"
    Public Function ThemHocSinh(ByVal hsDTO As HocSinhDTO) As Integer
        Dim sql = "insert into HocSinh(TenHocSinh,GioiTinh,NgaySinh,DiaChi,Email) values(@ten,@gioitinh,@ngaysinh,@diachi,@email)"
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, cnn)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@ten", OleDbType.VarChar)
        para.Value = hsDTO.Ten()

        para = cmd.Parameters.Add("@gioitinh", OleDbType.VarChar)
        para.Value = hsDTO.GT()

        para = cmd.Parameters.Add("@ngaysinh", OleDbType.Date)
        para.Value = hsDTO.NS()

        para = cmd.Parameters.Add("@diachi", OleDbType.VarChar)
        para.Value = hsDTO.DC()

        para = cmd.Parameters.Add("@email", OleDbType.VarChar)
        para.Value = hsDTO.Email()

        Dim i As Integer
        i = cmd.ExecuteNonQuery()
        If (i >= 0) Then
            Return 1 'them thanh cong
        Else
            Return 0 'them that bai
        End If
        cnn.Close()
    End Function
#End Region
#Region "Them1DongTrongDGView"
    Public Function Them1Dong(ByVal hsDTO As HocSinhDTO) As DataTable
        Dim newrow As DataRow
        Dim dt As DataTable = LayBangHS()
        newrow = dt.NewRow()
        newrow("MaHocSinh") = Convert.ToInt32(hsDTO.Ma)
        newrow("TenHocSinh") = hsDTO.Ten
        newrow("GioiTinh") = hsDTO.GT
        newrow("NgaySinh") = Convert.ToDateTime(hsDTO.NS.Date)
        newrow("DiaChi") = hsDTO.DC
        newrow("Email") = hsDTO.Email
        dt.Rows.Add(newrow)
        Return dt
    End Function
#End Region
#Region "KTHopLe"
    Public Function KTHopLe(ByVal hsDTO As HocSinhDTO) As Integer
        Dim sql, sql1 As String
        sql = "select TuoiToiThieu from ThamSo"
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand(sql, cnn)
        Dim tuoitt, tuoitd As Integer
        tuoitt = Convert.ToInt32(cmd.ExecuteScalar())
        sql1 = "select TuoiToiDa from ThamSo"
        cmd = New OleDbCommand(sql1, cnn)
        tuoitd = Convert.ToInt32(cmd.ExecuteScalar())
        Dim ngay As Date
        ngay = hsDTO.NS
        Dim year, tuoiht As Integer
        year = ngay.Year()
        tuoiht = Convert.ToInt32(Date.Now.Year - year)
        If (tuoiht >= tuoitt And tuoiht <= tuoitd) Then
            Return 1 'Hop le
        Else
            Return 0 'Khong hop le
        End If
    End Function
#End Region
End Class
